package edu.ynu.se.xiecheng.achitectureclass.dao;

import edu.ynu.se.xiecheng.achitectureclass.common.dao.LogicDAO;
import edu.ynu.se.xiecheng.achitectureclass.entity.SoilSensor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SoilSensorDao extends LogicDAO<SoilSensor, Long> {
    
    // 获取每种类型传感器的最新数据
    @Query(value = 
        "SELECT * FROM soil_sensor s1 WHERE (s1.name, s1.timestamp) IN " +
        "(SELECT s2.name, MAX(s2.timestamp) FROM soil_sensor s2 GROUP BY s2.name)", 
        nativeQuery = true)
    List<SoilSensor> findLatestDataForEachSensor();

    // 新增的查询方法
    @Query(value = "SELECT * FROM soil_sensor WHERE name = '土壤含水量' ORDER BY timestamp DESC", nativeQuery = true)
    List<SoilSensor> findAllSoilMoisture();

    @Query(value = "SELECT * FROM soil_sensor WHERE name = '土壤温度' ORDER BY timestamp DESC", nativeQuery = true)
    List<SoilSensor> findAllSoilTemperature();

    @Query(value = "SELECT * FROM soil_sensor WHERE name = '土壤氮含量' ORDER BY timestamp DESC", nativeQuery = true)
    List<SoilSensor> findAllSoilNitrogen();

    @Query(value = "SELECT * FROM soil_sensor WHERE name = '土壤磷含量' ORDER BY timestamp DESC", nativeQuery = true)
    List<SoilSensor> findAllSoilPhosphorus();

    @Query(value = "SELECT * FROM soil_sensor WHERE name = '土壤钾含量' ORDER BY timestamp DESC", nativeQuery = true)
    List<SoilSensor> findAllSoilPotassium();
}
